--    Author    : MaYasha
--    Name      : EDW.DS_DEPB_SUM.HQL
--    Functions : 
--    Purpose   : Monthly saving the variation data from detail
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0       2018-06-23  MaYasha           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE EDW.DS_DEPB_SUM PARTITION (DATA_DATE = '#V_DATA_DATE#' ,DATA_SRC_ORG = '#V_DATA_SRC_ORG#')
SELECT 
    FIN_ORG_NO                          -- 金融机构编码
    ,CLIENT_TYPE                        -- 客户类型
    ,DEPOSIT_TYPE                       -- 产品类别
    ,VALUE_DATE                         -- 存款协议起始日
    ,MATURITY_DATE                      -- 存款协议到期日
    ,CCY                                -- 存款币种
    ,SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0) ELSE 0 END) AS ACTUAL_BAL -- 存款余额
    ,IF_FLOAT                           -- 利率是否固定
    ,RATE                               -- 利率水平
    ,TERM_CODE                          -- 存款期限
    ,INTERVAL_CODE1                     -- 余额区间
    ,SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_AMOUNT,0) ELSE 0 END) AS ACTUAL_AMOUNT  -- 当月发生额
    ,INTERVAL_CODE2             -- 发生额区间
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0) ELSE 0 END) AS BAL_LM   -- 上期末余额
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0) ELSE 0 END) AS BAL_YF   -- 年初余额
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0) ELSE 0 END) AS BAL_LC   -- 去年同期余额
    ,SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_BAL,0) ELSE 0 END) AS WSUM_BAL   -- 余额加权值
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_BAL,0) ELSE 0 END) AS WBAL_LM    -- 上期末余额加权值
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_BAL,0) ELSE 0 END) AS WBAL_YF    -- 年初余额加权值
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_BAL,0) ELSE 0 END) AS WBAL_LC    -- 去年同期余额加权值
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_AMOUNT,0) ELSE 0 END AS AMOUNT_LM   -- 上期发生额
    ,SUM(CASE WHEN (DEP.DATA_DATE = '#V_DATA_DATE#' AND DEP.VALUE_DATE LIKE CONCAT(SUBSTRING('#V_DATA_DATE#',1,4),'%')) THEN NVL(ACTUAL_BAL,0) ELSE 0 END) AS AMOUNT_YS -- 当年累计发生额
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_AMOUNT,0) ELSE 0 END) AS AMOUNT_LC -- 去年同期发生额
    ,SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_AMOUNT,0) ELSE 0 END) AS WSUM_AMOUNT   -- 发生额加权值
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_AMOUNT,0) ELSE 0 END） AS WAMOUNT_LM    -- 上期末发生额加权值
    ,SUM(CASE WHEN (DEP.DATA_DATE = '#V_DATA_DATE#' AND DEP.VALUE_DATE LIKE CONCAT(SUBSTRING('#V_DATA_DATE#',1,4),'%')) THEN NVL(ACTUAL_BAL,0)*NVL(RATE,0) ELSE 0 END) AS WAMOUNT_YS    -- 当年累计发生额加权值
    ,0 -- SUM(CASE WHEN DEP.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_AMOUNT,0) ELSE 0 END) AS WAMOUNT_LC    -- 去年同期发生额加权值
    ,CF_FLAG                            -- 定活标识 
    ,ORG_TYPE                           -- 机构类型
FROM EDW.AG_DEPB_INFO DEP
WHERE ((DEP.DATA_DATE <= '#V_DATA_DATE#' AND DEP.DATA_DATE LIKE CONCAT(SUBSTRING('#V_DATA_DATE#',1,4),'%')) -- 当年截止本期
       OR DEP.DATA_DATE = '#V_DATA_DATE#' -- 上期
       OR DEP.DATA_DATE = '#V_DATA_DATE#' -- 年初
       OR DEP.DATA_DATE = '#V_DATA_DATE#' -- 去年同期
       )
AND DEP.DATA_SRC_ORG = '#V_DATA_SRC_ORG#'
GROUP BY FIN_ORG_NO    
         ,CLIENT_TYPE  
         ,DEPOSIT_TYPE 
         ,VALUE_DATE   
         ,MATURITY_DATE
         ,CCY 
         ,IF_FLOAT               
         ,RATE          
         ,TERM_CODE     
         ,INTERVAL_CODE1
         ,INTERVAL_CODE2
         ,CF_FLAG 
         ,ORG_TYPE